﻿Imports System.Data.SqlClient
Imports CapaEntidad
Imports System.Text.RegularExpressions

Public Class DatosCliente
    Dim cnn As SqlConnection
    Dim oCliente As New Cliente()
    Dim oCnn As New Conexion()
    Dim cmd As New SqlCommand()
    Dim vExito As Boolean

    Public Sub New()
        cnn = New SqlConnection(oCnn.ObtenerCadenaConexion())
    End Sub

    Public Function UltimoID() As String
        cmd.Connection = cnn
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "select max(codigo) + 1 from clientes"
        Dim uID As String = Nothing

        Try
            cnn.Open()
            uID = cmd.ExecuteScalar()
        Catch ex As Exception
            Return "Error"
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try

        Return uID
    End Function

    Public Function InsertarCliente(eCliente As Cliente) As Boolean
        cmd.Connection = cnn
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "proc_insertar"

        Try
            cmd.Parameters.Add("@codigo", SqlDbType.VarChar, 5).Value = eCliente.Codigo
            cmd.Parameters.Add("@nombres", SqlDbType.VarChar, 50).Value = eCliente.Nombres
            cmd.Parameters.Add("@apellidos", SqlDbType.VarChar, 100).Value = eCliente.Apellidos
            cmd.Parameters.Add("@correo", SqlDbType.VarChar, 100).Value = eCliente.Correo
            cmd.Parameters.Add("@fechanac", SqlDbType.Date).Value = IIf(eCliente.FechaNac Is Nothing, DBNull.Value, eCliente.FechaNac)

            cnn.Open()
            cmd.ExecuteNonQuery()
            vExito = True
        Catch ex As Exception
            vExito = False
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            cmd.Parameters.Clear()
        End Try

        Return vExito
    End Function

    Public Function ActualizarCliente(eCliente As Cliente) As Boolean
        cmd.Connection = cnn
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "proc_actualizar"

        Try
            cmd.Parameters.Add("@codigo", SqlDbType.VarChar, 5).Value = eCliente.Codigo
            cmd.Parameters.Add("@nombres", SqlDbType.VarChar, 50).Value = eCliente.Nombres
            cmd.Parameters.Add("@apellidos", SqlDbType.VarChar, 100).Value = eCliente.Apellidos
            cmd.Parameters.Add("@correo", SqlDbType.VarChar, 100).Value = eCliente.Correo
            cmd.Parameters.Add("@fechanac", SqlDbType.Date).Value = IIf(eCliente.FechaNac Is Nothing, DBNull.Value, eCliente.FechaNac)

            cnn.Open()
            cmd.ExecuteNonQuery()
            vExito = True
        Catch ex As Exception
            vExito = False
            Throw New Exception(ex.Message)
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            cmd.Parameters.Clear()
        End Try

        Return vExito
    End Function

    Public Function EliminarCliente(eCliente As Cliente) As Boolean
        cmd.Connection = cnn
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "proc_eliminar"

        Try
            cmd.Parameters.Add("@codigo", SqlDbType.VarChar, 5).Value = eCliente.Codigo
            cmd.Parameters.Add("@estado", SqlDbType.Int).Value = eCliente.Estado

            cnn.Open()
            cmd.ExecuteNonQuery()
            vExito = True
        Catch ex As Exception
            vExito = False
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            cmd.Parameters.Clear()
        End Try

        Return vExito
    End Function

    Public Function ListarClientes(parametro As String) As DataTable
        Dim ds As New DataSet

        Try
            cmd.Connection = cnn
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "proc_listar"
            cmd.Parameters.Add("@parametro", SqlDbType.VarChar, 100).Value = parametro

            Dim da As New SqlDataAdapter(cmd)

            da.Fill(ds, "Clientes")
        Catch ex As SqlException
            Throw New Exception(ex.Message)
        Finally
            cmd.Parameters.Clear()
        End Try

        Return ds.Tables("Clientes")
    End Function

    Public Function ConsultarCliente(codigo As String) As Cliente
        Try
            cmd.Connection = cnn
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "proc_consultar"
            cmd.Parameters.Add("@codigo", SqlDbType.VarChar, 5).Value = codigo

            If cnn.State = ConnectionState.Closed Then
                cnn.Open()
            End If

            Dim dr As SqlDataReader = cmd.ExecuteReader

            If dr.HasRows Then
                dr.Read()

                Dim fn As Nullable(Of Date)

                If dr("fechanac") Is DBNull.Value Then
                    fn = Nothing
                Else
                    fn = CDate(dr("fechanac").ToString)
                End If

                oCliente.Codigo = dr("codigo").ToString
                oCliente.Nombres = dr("nombres").ToString
                oCliente.Apellidos = dr("apellidos").ToString
                oCliente.Correo = dr("correo").ToString
                oCliente.FechaNac = fn
            End If

            cnn.Close()
            dr.Close()
            cmd.Parameters.Clear()
            Return oCliente
        Catch ex As Exception
            Throw New Exception(ex.Message)
        Finally
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            cmd.Parameters.Clear()
        End Try
    End Function
End Class
